<?php
/**
 * Database abstraction class.
 * Works with SQLite only at the moment.
 */
class AtbudgetDB extends SQLite3 {
	function __construct($fname)
	{
		$this->open($fname);
		$this->busyTimeout(2000);
	}
}

	$db = new AtbudgetDB('../atbudget.sqlite');
	try {
		switch ($_SERVER['REQUEST_METHOD']) {
			case 'GET': //list accounts
				header('Content-type: application/json');
				$res = $db->query('SELECT * FROM account');
				$accounts = array();
				$i = 0;
				if ($res) {
					while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
						//fetch account currency
						$res1 = $db->query('SELECT * FROM currency WHERE id='.$row['currencyid'].' ORDER BY id');
						$row1 = $res1->fetchArray(SQLITE3_ASSOC);
						$row['currency'] = $row1;
						//calculate account balance
						$resDebit  = $db->query('SELECT SUM(`amount`) AS `debit` FROM `transaction` WHERE fromaccount='.intval($row['id']));
						$rowDebit  = $resDebit->fetchArray(SQLITE3_ASSOC);
						$resCredit = $db->query('SELECT SUM(`amount`/`rate`) AS `credit` FROM `transaction` WHERE toaccount='.intval($row['id']));
						$rowCredit = $resCredit->fetchArray(SQLITE3_ASSOC);
						$row['balance'] = floatval($rowCredit['credit']) - floatval($rowDebit['debit']) + floatval($row['initbalance']);
						$accounts[$i] = $row;
						$i++;
					}
				}
				echo json_encode(array('success' => 'true', 'accounts' => $accounts));
				break;
			case 'POST': //new account
				header('Content-type: application/json');
				$acc = json_decode($HTTP_RAW_POST_DATA);
				$res = $db->query('SELECT id FROM currency WHERE code="'.$acc->currency_code.'"');
				$row = $res->fetchArray(SQLITE3_ASSOC);
				$cid = $row['id'];
				if ($db->exec('INSERT INTO account (name, description, currencyid, initbalance) VALUES ("'.$acc->name.'","'.$acc->description.'",'.$cid.','.$acc->initbalance.')')) {
					$res1 = $db->query('SELECT * FROM currency WHERE id='.$cid);
					$row1 = $res1->fetchArray(SQLITE3_ASSOC);
					$acc->currency = $row1;
					$acc->id = $db->lastInsertRowID();
					echo json_encode(array('success' => 'true','accounts' => array($acc)));
				} else {
					echo json_encode(array('success' => 'false', 'msg' => 'Failed to create record.','accounts' => array()));
				}
				break;
			case 'PUT': //save account
				header('Content-type: application/json');
				echo json_encode(array('success' => 'false', 'msg' => 'Not implemented yet.', 'accounts' => array()));
				break;
			case 'DELETE': //delete account
				header('Content-type: application/json');
				echo json_encode(array('success' => 'false', 'msg' => 'Not implemented yet.', 'accounts' => array()));
				break;
			default:
				header('Content-type: application/json');
				echo json_encode(array('success' => 'false', 'msg' => 'Server cannot handle your request.','accounts' => array()));
		}//switch
	} catch (Exception $e) {
		header('Content-type: application/json');
		echo json_encode(array('success' => 'false', 'msg' => "Exception occurred.\n"+$e->getMessage(), 'accounts' => array()));
	}
	$db->close();
